﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using fideeraSoft.Utils;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace fideeraSoft.Utils
{
    class SQLclass
    {

        // C#
        public void ConnectToSql()
        {
            //System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            // TODO: Modify the connection string and include any
            // additional required properties for your database.
            //conn.ConnectionString ="integrated security=SSPI;database=trazab.dbo ;data source=(local); persist security info=False;initial catalog=northwind";

           // string connStr = System.Configuration.ConfigurationManager.AppSettings["trazabConnString"] ;

            SqlConnection conn = conectarSQL();

            try
            {
                conn.Open();
                // Insert code to process data.
                MessageBox.Show("Conectado");
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to data source: "+ex);
            }
            finally
            {
                conn.Close();
            }
        }

        public static SqlConnection conectarSQL()
        {
            SqlConnection conn=null;
            try{

                string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["trazabConnString"].ConnectionString;
                conn = new SqlConnection(connStr);
            }catch(Exception e)
            {
                throw new MiException("[SqlConnection] Error at connect: "+e);
            }
            return conn;
        }

        public static bool tryToConnectToSQL()
        {
            System.Data.SqlClient.SqlConnection conn = null;
            try
            {
                //MessageBox.Show("QUIERO CONECTAR", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);//debug

                conn = SQLclass.conectarSQL();

                conn.Open();                
            }            
            catch (MiException e)
            {
                MessageBox.Show("Error al conectar DB\n" + e, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                CLogger.Append("Error al conectar DB: " + e, CLogger.ERROR);      //DEBUG
                return false;
            }
            catch (Exception e)
            {
                MessageBox.Show("Error al conectar DB\n" + e, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error);
                CLogger.Append("Error al conectar DB: " + e, CLogger.ERROR);      //DEBUG
                return false;
            }
            finally
            {
                conn.Close();

            }
            return true;
        }

        public void createUserDB(String login,String user,String pass) 
        {
            SqlConnection conn=null;
            try{
                
                conn = conectarSQL();

                conn.Open();

                string query = "CREATE LOGIN " + login + " WITH PASSWORD ='" + pass + "'";
                SqlCommand cmd = new SqlCommand(query,conn);
                SqlDataReader reader;
                
                commandQuery(conn, cmd, query);
                reader=cmd.ExecuteReader();
                
                
                string query2= "CREATE USER "+user+" FOR LOGIN "+login;
                commandQuery(conn, cmd, query);
                reader = cmd.ExecuteReader();
                //cmd.Connection.Close();
                /*DataTable dt = new DataTable();
                DA.Fill(dt);
                */
            }catch(Exception e)
            {
                throw new MiException("[createUserDB] Exception en create user: "+e);
            }finally
            {
            
                conn.Close();
            }
        }

        public static void commandQuery(SqlConnection conn, SqlCommand cmd, string query)
        {
            cmd.CommandText = query;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;
        }

        #region listarLogins    
        public DataTable listarLogins()
        {
            SqlConnection conn = null;
            DataTable t = new DataTable();
            SqlDataReader reader;
            try
            {                
                conn = conectarSQL();
                conn.Open();

                //string query = "select * from master..syslogins";
                string query = "select * from trazab..syslogins";
                //string query = "select * from trazab.dbo.syslogins";
                //string query = "select * from master.dbo.syslogins";

                //SqlCommand cmd = new SqlCommand(query, conn);
                //commandQuery(conn, cmd, query);
                //reader = cmd.ExecuteReader();
                //while (reader.Read())
                //{
                //    MessageBox.Show(reader[0].ToString());

                //}

                SqlDataAdapter dataAdapter = new SqlDataAdapter();                               
                dataAdapter.SelectCommand = new SqlCommand(query, conn);
                dataAdapter.Fill(t);
                 
                
            }
            catch (Exception e)
            {
                throw new MiException("[createUserDB] Exception en Listar Logins: " + e);
            }
            finally
            {
                conn.Close();
            }
            return t;
        }
        #endregion

        #region Obtner Adapter por query
        public static SqlDataAdapter obtenerAdapter(String query)
        {
            SqlConnection conn = SQLclass.conectarSQL();
            SqlDataAdapter adapter=null;
            

            try
            {
                conn.Open();
                adapter = new SqlDataAdapter(query, conn);

            }
            catch (Exception e)
            {
                CLogger.Append("[obtenerAdapter] Exception : " + e, CLogger.ERROR);      //DEBUG
                throw new MiException("[obtenerAdapter] Exception: " + e);
            }
            finally
            {
                conn.Close();
            }
            return adapter;
        }
        #endregion

    }
}
